package com.bw.dao;

import com.bw.pojo.Student;
import com.bw.util.JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Queue;

/***************************
 FileAction: 对学生表进行增删改查的数据访问层类

 CreateTime:2022-03-26 10:48
 Author:Aaking-aline
 ****************************/
public class StudentDao {

    public int insertStudent(Student stu) throws SQLException {
        String sql = "insert into student values(null,?,?,?,?)";

        Connection connection = JDBC.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setObject(1,stu.getName());
        ps.setObject(2,stu.getAge());
        ps.setObject(3,stu.getAddress());
        ps.setObject(4,stu.getPhoneNumber());

        return ps.executeUpdate();
    }

    public int deleteStudent(String name) throws SQLException {
        String sql = "delete from student where name = ?";

        Connection connection = JDBC.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setObject(1,name);

        return ps.executeUpdate();
    }

    public int deleteStudent(String id,String name) throws SQLException {
        String sql = "delete from student where id = ? and name = ?";

        Connection connection = JDBC.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setObject(1,id);
        ps.setObject(2,name);

        return ps.executeUpdate();
    }

    public int updateStudent(Student stu) throws SQLException {
        String sql = "update student set address = ?,phoneNumber = ? where id = ?";

        Connection connection = JDBC.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setObject(1,stu.getAddress());
        ps.setObject(2,stu.getPhoneNumber());
        ps.setObject(3,stu.getId());

        return ps.executeUpdate();
    }

    public List<Student> selectStudent() throws SQLException {
        List<Student> list = new ArrayList<>();
        String sql = "select * from student";

        Connection connection = JDBC.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        while(rs.next()){
            Student student = new Student(rs.getInt(1),
                    rs.getString(2),
                    rs.getInt(3),
                    rs.getString(4),
                    rs.getString(5));
            list.add(student);
        }

        return list;
    }

    public List<Student> selectStudentByName(String name) throws SQLException {
        List<Student> list = new ArrayList<>();
        String sql = "select * from student where name = ?";

        Connection connection = JDBC.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setObject(1,name);

        ResultSet rs = ps.executeQuery();

        while(rs.next()){
            Student student = new Student(rs.getInt(1),
                    rs.getString(2),
                    rs.getInt(3),
                    rs.getString(4),
                    rs.getString(5));
            list.add(student);
        }

        return list;
    }

    public List<Student> selectStudentByLimit(String index,String number) throws SQLException {
        List<Student> list = new ArrayList<>();
        String sql = "select * from student limit "+ index +"," + number;

        Connection connection = JDBC.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        while(rs.next()){
            Student student = new Student(rs.getInt(1),
                    rs.getString(2),
                    rs.getInt(3),
                    rs.getString(4),
                    rs.getString(5));
            list.add(student);
        }

        return list;
    }







}
